package pandora.data.mysql;


import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import pandora.connection.ConnectionManager;
import pandora.data.ICommentDAO;
import pandora.data.ICustomerDAO;
import pandora.data.IStoreDAO;
import pandora.dto.CommentDTO;
import pandora.dto.CustomerDTO;
import pandora.dto.StoreDTO;
import pandora.utils.UIDGenerator;

public class JDBCCommentDAO implements ICommentDAO {
	
	private Connection conn;

	public JDBCCommentDAO(){
		conn = ConnectionManager.getInstance().checkOut();
	}
	
    protected void finalize() {
        ConnectionManager.getInstance().checkIn(conn);
    }
	
	public List<CommentDTO> getCommentsByStore(String nif) {
		
		List<CommentDTO> comentarios;
		comentarios = new ArrayList<CommentDTO>();
		ResultSet rs = null;
		Statement stmt = null;
		
		try {
			stmt = conn.createStatement();
			
			IStoreDAO storeDAO = new JDBCStoreDAO();
			
			String OID = storeDAO.selectStoreOIDByNIF(nif);
			
			String sql = "select c.*, s.NIF, cu.customerDNI from Comments c, Stores s, Customers cu where c.storeOID = '" + OID + "' and s.OID = c.storeOID and cu.OID = c.customerOID order by date";
			rs = stmt.executeQuery(sql);
			
			IStoreDAO storeDao = new JDBCStoreDAO();
			ICustomerDAO customerDao = new JDBCCustomerDAO();
						
			while (rs.next()){
				CommentDTO com = new CommentDTO();
								
				com.setStore(storeDao.getStoreByNIF(rs.getString("NIF")));
				com.setCustomer(customerDao.getCustomerByDNI(rs.getString("customerDNI")));
								
				com.setNote(rs.getString("text"));
				com.setDateOfComment(new Date(rs.getLong("date")));
				
				comentarios.add(com);
			}			
		
		 } catch (SQLException e) {
	            System.out.println("Message: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	     } finally {
	            try {
	                if (rs != null)
	                    rs.close();
	                if (stmt != null)
	                    stmt.close();
	            } catch (SQLException e) {
	            	System.out.println("RESULTSET CLOSING ERROR OR STATEMENT CLOSING ERROR");
	            }
	     }
		
		return comentarios;
	}

	public void saveComment(CommentDTO comentario) {
		String nota = comentario.getNote();
		CustomerDTO usuario = comentario.getCustomer();
		StoreDTO comercio = comentario.getStore();
		
		String OID = UIDGenerator.getInstance().getKey();
		
		IStoreDAO storeDAO = new JDBCStoreDAO();
		ICustomerDAO customerDAO = new JDBCCustomerDAO();
		
		String comercioOID = storeDAO.selectStoreOIDByNIF(comercio.getStoreNIF());
		
		String usuarioOID = customerDAO.selectCustomerOIDByDNI(usuario.getCustomerDNI());
		
		Long today = System.currentTimeMillis();
		
		System.out.println(OID);
		System.out.println(comercioOID);
		System.out.println(usuarioOID);
		System.out.println(nota);
		System.out.println(today);
		
		String sql = "insert into Comments (OID, storeOID,customerOID,text,date) values ('"+OID+"','"+comercioOID+"','"+usuarioOID+"','"+nota+"',"+today+")";
		System.out.println(sql);		
				
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
		    System.out.println("Message: " + e.getMessage());
		    System.out.println("SQLState: " + e.getSQLState());
		    System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
		    try {
		    	if (stmt != null)
		            stmt.close();
		        } catch (SQLException e) {
		        	System.out.println("RESULTSET CLOSING ERROR OR STATEMENT CLOSING ERROR");
		        }
		    }
		}

	}
